CREATE OR REPLACE TYPE SPLIT_TBL 
AS TABLE OF varchar2(1000);


alter table casemast add (
	c_status_date date,
	c_status_date2 date,
	c_process varchar2(4),
	c_process2 varchar2(4),
	c_created_date2 date
);

alter table casehist add (
  ch_process varchar2(4),
  ch_process_start_date date
);

alter table statusmast add (
  s_process varchar2(4)
);


CREATE TABLE "MAPADMIN"."RPTTERMINALINSTALL" 
(	"RPT_DATE" DATE NOT NULL ENABLE, 
"RPT_CASE_ID" NUMBER(16,0) NOT NULL ENABLE, 
"RPT_OUTLET_ID" NUMBER(16,0) NOT NULL ENABLE, 
"RPT_VENDOR" VARCHAR2(50 BYTE), 
"RPT_SERIAL_NO" VARCHAR2(50 BYTE), 
"RPT_AREA" VARCHAR2(50 BYTE), 
"RPT_JOB_TYPE" VARCHAR2(20 BYTE), 
"RPT_STOCK" VARCHAR2(20 BYTE), 
"RPT_CREATOR" VARCHAR2(50 BYTE), 
"RPT_SENT_DATE" DATE, 
"RPT_COMPLETED_DATE" DATE, 
"RPT_SLA" NUMBER(4,0), 
"RPT_RESCHEDULE_DATE" DATE, 
"RPT_STATUS" VARCHAR2(50 BYTE), 
"RPT_WIRELESS_STOCK" VARCHAR2(200 BYTE), 
"RPT_DEBIT" VARCHAR2(1 BYTE), 
"RPT_CREATED_DATE" DATE NOT NULL ENABLE, 
"RPT_MERCHANT_NAME" VARCHAR2(200 BYTE), 
"RPT_ADDRESS" CLOB, 
"RPT_AMEX_POS" VARCHAR2(20 BYTE), 
"RPT_AMEX_MOTO" VARCHAR2(20 BYTE), 
"RPT_PARTNERS" CLOB, 
"RPT_POS_MID" VARCHAR2(20 BYTE), 
"RPT_POS_TID" VARCHAR2(20 BYTE), 
"RPT_EZPAY" CLOB, 
"RPT_EZPAY_AMEX" CLOB, 
"RPT_ACCEPTANCE" CLOB, 
"RPT_OTHERS" CLOB, 
"RPT_HOTEL_FUNCTION" CLOB, 
"RPT_REMARK" CLOB
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" 
LOB ("RPT_ADDRESS") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RPT_PARTNERS") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RPT_EZPAY") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RPT_EZPAY_AMEX") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RPT_ACCEPTANCE") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RPT_OTHERS") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RPT_HOTEL_FUNCTION") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RPT_REMARK") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

CREATE INDEX "MAPADMIN"."RPTTERMINALINSTALL_INDEX1" ON "MAPADMIN"."RPTTERMINALINSTALL" ("RPT_DATE", "RPT_CASE_ID", "RPT_OUTLET_ID", "RPT_VENDOR") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;


CREATE TABLE "MAPADMIN"."RPTTRF" 
(	"RPT_DATE" DATE NOT NULL ENABLE, 
"RPT_CASE_ID" NUMBER(16,0) NOT NULL ENABLE, 
"RPT_OUTLET_ID" NUMBER(16,0) NOT NULL ENABLE, 
"RPT_MID" VARCHAR2(20 BYTE), 
"RPT_TID" VARCHAR2(20 BYTE), 
"RPT_TERMINAL_TYPE" VARCHAR2(20 BYTE), 
"RPT_VENDOR" VARCHAR2(200 BYTE), 
"RPT_ACCOUNT" VARCHAR2(20 BYTE), 
"RPT_BUS_REG" VARCHAR2(20 BYTE), 
"RPT_RENTAL" NUMBER(16,2), 
"RPT_INSTALL_DATE" DATE, 
"RPT_CHEQUE_NO" VARCHAR2(20 BYTE), 
"RPT_CHEQUE_DATE" VARCHAR2(20 BYTE), 
"RPT_BANK_BRANCH" VARCHAR2(200 BYTE), 
"RPT_AMOUNT" NUMBER(16,2), 
"RPT_ADV_AMOUNT" VARCHAR2(20 BYTE), 
"RPT_1ST_RENTAL" VARCHAR2(20 BYTE), 
"RPT_REMARKS" VARCHAR2(20 BYTE), 
"RPT_CREATOR" VARCHAR2(50 BYTE), 
"RPT_CREATED_DATE" DATE
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

CREATE INDEX "MAPADMIN"."RPTTRF_INDEX1" ON "MAPADMIN"."RPTTRF" ("RPT_DATE", "RPT_CASE_ID", "RPT_OUTLET_ID", "RPT_MID") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;


CREATE TABLE "MAPADMIN"."RPTCASESTATUS" 
(	"RCS_ID" NUMBER, 
"RCS_YEAR" NUMBER, 
"RCS_MONTH" NUMBER, 
"RCS_CASE_ID" NUMBER, 
"RCS_STATUS" VARCHAR2(100 BYTE), 
"RCS_CREATED_DATE" DATE, 
"RCS_MODIFIED_DATE" DATE, 
"RCS_STATUS_DATE" DATE, 
"RCS_ACTION" VARCHAR2(100 BYTE), 
"RCS_MATRIX" VARCHAR2(100 BYTE), 
"RCS_CREATED_BY" VARCHAR2(20 BYTE), 
"RCS_BRANCH" NUMBER(*,0)
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

CREATE OR REPLACE TRIGGER "MAPADMIN"."TRIGGER_RPTCASESTATUS_INSERT" 
BEFORE INSERT ON RPTCASESTATUS 
FOR EACH ROW 
DECLARE
  reportID RPTCASESTATUS.RCS_ID%TYPE;
  
BEGIN

  select SEQ_RPTCASESTATUS.nextval into reportID from dual;  
  :NEW.RCS_ID := reportID;
  
END;
/
ALTER TRIGGER "MAPADMIN"."TRIGGER_RPTCASESTATUS_INSERT" ENABLE;

CREATE SEQUENCE  "MAPADMIN"."SEQ_RPTCASESTATUS"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 4921 CACHE 20 NOORDER  NOCYCLE ;

CREATE TABLE "MAPADMIN"."RPTCASESTATUSDAILY" 
(	"RCSD_ID" NUMBER, 
"RCSD_DATE" DATE, 
"RCSD_CASE_ID" NUMBER, 
"RCSD_STATUS" VARCHAR2(100 BYTE), 
"RCSD_CREATED_DATE" DATE, 
"RCSD_MODIFIED_DATE" DATE, 
"RCSD_STATUS_DATE" DATE, 
"RCSD_ACTION" VARCHAR2(100 BYTE), 
"RCSD_MATRIX" VARCHAR2(100 BYTE), 
"RCSD_CREATED_BY" VARCHAR2(20 BYTE), 
"RCSD_BRANCH" NUMBER(*,0)
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;

CREATE OR REPLACE TRIGGER "MAPADMIN"."TRIGGER_RPTCSDAILY_INSERT" 
before insert on "MAPADMIN"."RPTCASESTATUSDAILY" 
for each row 
begin  
   if inserting then 
	  if :NEW."RCSD_ID" is null then 
		 select SEQ_RPTCASESTATUSDAILY.nextval into :NEW."RCSD_ID" from dual; 
	  end if; 
   end if; 
end;

/
ALTER TRIGGER "MAPADMIN"."TRIGGER_RPTCSDAILY_INSERT" ENABLE;

CREATE SEQUENCE  "MAPADMIN"."SEQ_RPTCASESTATUSDAILY"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 4281 CACHE 20 NOORDER  NOCYCLE ;


CREATE TABLE "MAPADMIN"."RPTSLAPROCESS" 
   (	"RPT_DATE" DATE NOT NULL ENABLE, 
	"RPT_CASE_ID" NUMBER(16,0) NOT NULL ENABLE, 
	"RPT_MATRIX" VARCHAR2(1 BYTE), 
	"RPT_PROCESS" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"RPT_SLA" NUMBER(4,0) NOT NULL ENABLE, 
	"RPT_CREATED_DATE" DATE NOT NULL ENABLE, 
	 CONSTRAINT "RPTSLAPROCESS_UK1" UNIQUE ("RPT_DATE", "RPT_CASE_ID", "RPT_PROCESS")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
